package com.cpi.cornpp.languaje.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.languaje.dto.LanguageDTO;
import com.cpi.cornpp.languaje.exception.LanguageException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Languaje Data Access
 * @author Edgar Joao
 */
public class LanguageDAO extends DBDAO{
	/** Datasource name */
	private static final String dsName = "cornpp";
	/** SQL Queries*/
	private static final String SQL_SELECT_LIST_OF_LANGUAGES = "SELECT language_id, description, app_default FROM languages order by app_default desc";
	private static final String SQL_INSERT_USER_LANGUAGE = "INSERT INTO users_languages (user_id, language_id) VALUES (?, ?)";
	private static final String SQL_SELECT_LANGUAGES_BY_USER_ID_AND_LANGUAGE_ID = "SELECT user_id FROM users_languages where user_id =?";
	private static final String SQL_UPDATE_USER_LANGUAGE = "UPDATE users_languages SET language_id = ? WHERE user_id = ?";
	
	/**
	 * Get list of Languages
	 * @return
	 * @throws Exception
	 */
	public List getListOfLanguajes() throws LanguageException{
		LanguageDTO languageDTO = null;
		ArrayList arrayResult = null;		
		PreparedStatement preparedStatement =  null;
		boolean isPoolException = false;
		try {
			arrayResult = new ArrayList(0);
			preparedStatement =  prepareSQL(SQL_SELECT_LIST_OF_LANGUAGES, dsName);			
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				languageDTO = new LanguageDTO();
				languageDTO.setId(rst.getString(1));
				languageDTO.setDescription(rst.getString(2));
				languageDTO.setAppDefault(rst.getBoolean(3));
				arrayResult.add(languageDTO);
			}
		} catch (PoolException poolException){
			LanguageException languageException=null;
			poolException.printStackTrace(System.out);
			languageException= new LanguageException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			isPoolException = true;
			throw languageException;
		} catch (SQLException sqlException){
			LanguageException languageException = null;
			sqlException.printStackTrace(System.out);
			languageException= new LanguageException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw languageException;
		}finally {	
			try{
				if( !isPoolException ){
					closeConnection(preparedStatement.getConnection());
				}
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	
	/**
	 * 
	 * @param languageDTO
	 * @return
	 * @throws Exception
	 */
	public boolean insertLanguageUser(LanguageDTO languageDTO) throws LanguageException{
		int insertedRows = 0;
		PreparedStatement pstm = null;
		
		try{
			
			if (languageDTO != null){
				pstm = prepareSQL(SQL_INSERT_USER_LANGUAGE, dsName);				
				pstm.setString(1, languageDTO.getUserDTO().getUsrId());
				pstm.setString(2, languageDTO.getId());								
				insertedRows = executeInsert(pstm);
			}
			
		} catch (PoolException poolException){
			LanguageException languageException=null;
			poolException.printStackTrace(System.out);
			languageException= new LanguageException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw languageException;
		} catch (SQLException sqlException){
			LanguageException languageException = null;
			sqlException.printStackTrace(System.out);
			languageException= new LanguageException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw languageException;
		} finally {
			try{				
				closeConnection(pstm.getConnection());				
			} catch(SQLException sqle){
				
			}			
		}
		
		return (insertedRows>0);
	}
	
	/**
	 * 
	 * @param languageDTO
	 * @return
	 * @throws Exception
	 */
	public boolean existUserAndLanguage(LanguageDTO languageDTO) throws LanguageException{			
		PreparedStatement preparedStatement =  null;
		boolean returnResult = false;
		try {	
			preparedStatement =  prepareSQL(SQL_SELECT_LANGUAGES_BY_USER_ID_AND_LANGUAGE_ID, dsName);
			preparedStatement.setString(1, languageDTO.getUserDTO().getUsrId());
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()){
				returnResult = true;											
			}
		} catch (PoolException poolException){
			LanguageException languageException=null;
			poolException.printStackTrace(System.out);
			languageException= new LanguageException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw languageException;
		} catch (SQLException sqlException){
			LanguageException languageException = null;
			sqlException.printStackTrace(System.out);
			languageException= new LanguageException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw languageException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return returnResult;
	}
	
	/**
	 * 
	 * @param languageDTO
	 * @return
	 * @throws Exception
	 */
	public boolean updateIfExistUserLanguage(LanguageDTO languageDTO) throws LanguageException{
		int insertedRows = 0;
		PreparedStatement pstm = null;
		
		try{
			
			if (languageDTO != null){
				pstm = prepareSQL(SQL_UPDATE_USER_LANGUAGE, dsName);				
				pstm.setString(1, languageDTO.getId());
				pstm.setString(2, languageDTO.getUserDTO().getUsrId());								
				insertedRows = executeInsert(pstm);
			}
			
		} catch (PoolException poolException){
			LanguageException languageException=null;
			poolException.printStackTrace(System.out);
			languageException= new LanguageException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw languageException;
		} catch (SQLException sqlException){
			LanguageException languageException = null;
			sqlException.printStackTrace(System.out);
			languageException= new LanguageException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw languageException;
		}finally {
			try{				
				closeConnection(pstm.getConnection());				
			} catch(SQLException sqle){
				
			}			
		}
		
		return (insertedRows>0);
	}
	
}
